package com.topboya.officeuser.excel;

import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.xml.sax.*;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.regex.Pattern;


/**
 * XSSF and SAX (Event API) basic example.
 * See  for a fuller example of doing
 *  XSLX processing with the XSSF Event code.
 */
public class MyExcel2007ForMaxRow {
	/**
	 * 代表Excel中必须有值的起始列(A、B、C....AA、AB...)
	 */
	private static final String INDEX_COL_DATA = "A";
	public static final String NEW_ROW_REGEX = "^"+INDEX_COL_DATA+"[0-9]+$";
	//new add
	public long maxRow = 0;//记录总行数
	
	/**
	 * 带路径的文件名
	 */
	//private String filename = null;
	private File f = null;
	/*public MyExcel2007ForMaxRow(String path) throws Exception{
		if(StringUtils.isBlank(path)) throw new Exception("文件名不能空");
		this.f = new File(path);
		
		if(!f.exists()) throw new Exception("文件不存在");
		processFirstSheet();
	}*/
	public MyExcel2007ForMaxRow(File f) throws IOException, SAXException, OpenXML4JException{
		this.f = f;
		processFirstSheet();
	}
	/**
	 * 指定获取第一个sheet
	 * @param filename
	 * @throws IOException 
	 * @throws SAXException 
	 * @throws OpenXML4JException 
	 * @throws Exception
	 */
	private void processFirstSheet() throws IOException, SAXException, OpenXML4JException  {
		//OPCPackage pkg = OPCPackage.open(filename);
		OPCPackage pkg = OPCPackage.open(f);
		XSSFReader r = new XSSFReader( pkg );
		SharedStringsTable sst = r.getSharedStringsTable();
 
		XMLReader parser = fetchSheetParser(sst);
 
		// To look up the Sheet Name / Sheet Order / rID,
		//  you need to process the core Workbook stream.
		// Normally it's of the form rId# or rSheet#
		InputStream sheet2 = r.getSheet("rId1");
		InputSource sheetSource = new InputSource(sheet2);
		parser.parse(sheetSource);
		sheet2.close();//关闭流
		pkg.close();//关闭POI的sax流
	}
	
	private XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
		XMLReader parser =
			XMLReaderFactory.createXMLReader(
					"org.apache.xerces.parsers.SAXParser"
			);
		ContentHandler handler = new MaxRowHandler();
		parser.setContentHandler(handler);
		return parser;
	}
 
	/** 
	 * See org.xml.sax.helpers.DefaultHandler javadocs 
	 */
	private  class MaxRowHandler extends DefaultHandler {
 
		@Override
		public void startElement(String uri, String localName, String name,
				Attributes attributes) throws SAXException {
			// c => cell
			if("c".equals(name)) {
				String index = attributes.getValue("r");
				if(Pattern.compile(NEW_ROW_REGEX).matcher(index).find()){
					//System.out.println(index);
					maxRow++;
				}
		
			}
 
		}
	
	}
	public static void main(String[] args) throws IOException, SAXException, OpenXML4JException {
		System.out.println(System.currentTimeMillis());
		MyExcel2007ForMaxRow reader = new MyExcel2007ForMaxRow(new File("C:\\Users\\boya\\Desktop\\公司资料\\3-5月份竟品.xlsx"));
		System.out.println(System.currentTimeMillis());
		System.out.println(reader.maxRow);
	}
}